/*
 * provides the basic database connection and allows db queries
 * works further as combination of factory and multiton pattern and
 * returns entities by their primary keys
 */

package database;

import accounts.Account;
import accounts.CheckingAccount;
import itsbank.Customer;
import java.sql.*;
import java.util.Hashtable;

/**
 * @author Mike
 */
public class DatabaseManager {
    private Connection con;
    private static DatabaseManager instance;
    private Hashtable<Integer,Account> accounts;
    private Hashtable<Integer,Customer> customers;

    private DatabaseManager() throws Exception {
        Class.forName( "com.mysql.jdbc.Driver" );
        con = DriverManager.getConnection( "jdbc:mysql://127.0.0.1:3306/itsbank", "root", "");
        accounts = new Hashtable<Integer,Account>();
        customers = new Hashtable<Integer,Customer>();
    }
   /**
    * returns the instance of the db manager
    * @return the instance of the manager
    */
    public static DatabaseManager getInstance() {
        if (instance == null) {
            try {
                instance = new DatabaseManager();
            }
            catch (Exception e) {
                System.out.println("db connection failed!");
                System.exit(-1);
            }
        }
        return instance;
    }
    /**
     * execute a query that returns data
     * @param sql a sql statement
     * @return the result set for your query
     * @throws SQLException
     */
    public ResultSet query(String sql) throws SQLException {
        Statement st = con.createStatement();
        return st.executeQuery(sql);
    }
    /**
     * execute a query that doesn't return data (insert etc.)
     * @param sql a sql statement
     * @throws SQLException
     */
    public void update(String sql) throws SQLException {
        Statement st = con.createStatement();
        st.execute(sql);
    }
    /**
     * returns a account identified by it's number
     * @param number the account number
     * @return the account
     * @throws SQLException
     */
    public Account getAccountByNumber(int number) throws SQLException {
        Account acc = accounts.get(number);
        if (acc == null) {
            ResultSet rs = DatabaseManager.getInstance().query("SELECT * FROM account WHERE AccountNumber = "+number);
            if (!rs.first()) {
                throw new SQLException();
            }
            acc = new CheckingAccount(rs.getInt("BankID"),rs.getInt("AccountNumber"),rs.getInt("OwnerID"),rs.getInt("Balance"),rs.getInt("Limit"));
            accounts.put(number, acc);
        }
        return acc;
    }
    /**
     * returns a customer identified by it's customer id
     * @param ID the id of the customer
     * @return the customer
     * @throws SQLException
     */
    public Customer getCustomerByID(int ID) throws SQLException {
        Customer cust = customers.get(ID);
        if (cust == null) {
            ResultSet rs = DatabaseManager.getInstance().query("SELECT * FROM customers WHERE ID = "+ID);
            if (!rs.first()) {
                throw new SQLException();
            }
            cust = new Customer(rs.getInt("ID"),rs.getString("Name"),rs.getString("Street"),rs.getString("Phone"),rs.getInt("PIN"),rs.getInt("Money"));
            customers.put(ID, cust);
        }
        return cust;
    }
}
